package com.cl.master.mapper;

import com.cl.master.domain.WaterQuality;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * @Author xc
 * @Date 2023/4/26 16:18
 * @Version 1.0
 */
@Mapper
@Component//idea高版本不会警告错误，可省略
public interface WaterQualityMapper{

/*数据可视化*/
    //查询全部数据
    @Select("select mid ,water_temperature, turbidity, dissolved_oxygen, conductivity, ph from water_quality where mid=#{mid}")
    Map<String, Object> getDataByMid(int mid);

    //暂无
    @Select("select mid ,site_condition,water_temperature, turbidity, dissolved_oxygen, conductivity, ph from water_quality where mid=#{mid}")
    Map<String, Object> getDataByxMid(int mid);

    //含氧量大于6的污水数（暂无）
    @Select("SELECT COUNT(*) FROM water_quality WHERE dissolved_oxygen > 6")
    int countByDissolvedOxygen();

    //含氧量大于6的污水数和总数据量
    @Select("SELECT COUNT(*) as total_count, " +
            "SUM(CASE WHEN dissolved_oxygen > 6 THEN 1 ELSE 0 END) as condition_count " +
            "FROM water_quality")
    Map<String,Object> getCount();


    //获取站点状态信息
    @Select("SELECT COUNT(*) as total_count, " +
            "SUM(CASE WHEN site_condition = '正常' THEN 1 ELSE 0 END) as condition_count " +
            "FROM water_quality")
    Map<String, Object> getStatusBy();


    //获取污水浊度状态信息
    @Select("SELECT COUNT(*) as total_count, " +
            "SUM(CASE WHEN turbidity <= 20  THEN 1 ELSE 0 END) as condition_count " +
            "FROM water_quality")
    Map<String, Object> getStandardBy();


    //获取平均ph以及省市、流域的信息
    @Select("SELECT AVG(PH) AS average_ph, " +
            "SUM(CASE WHEN ph < 9 AND ph > 8 THEN 1 ELSE 0 END) AS condition_count, " +
            "COUNT(DISTINCT Province_City) AS province_city_count, " +
            "COUNT(DISTINCT Watershed_River) AS watershed_count " +
            "FROM water_quality")
    Map<String, Object> getPHBy();


    //动态信息溶解氧与浊度信息
    @Select("SELECT dissolved_oxygen, turbidity " +
            "FROM water_quality")
    List<Map<String,Object>> getDissolvedOxygenAndTurbidity();

    @Select("SELECT COUNT(*) as total_count " +
            "FROM water_quality")
    int getTotalCount();

    //获取ph以及省市、流域的状态信息，暂无
    @Select("SELECT AVG(PH) AS average_ph, " +
            "SUM(CASE WHEN ph < 9 AND ph > 8 THEN 1 ELSE 0 END) AS condition_count, " +
            "FROM water_quality")
    Map<String, Object> getCityAndWatershedBy();




    //正常达标数据
    @Select("SELECT COUNT(*) as total_count, " +
            "SUM(CASE WHEN site_condition = '正常' THEN 1 ELSE 0 END) as condition_count1, " +
            "SUM(CASE WHEN turbidity <= 20  THEN 1 ELSE 0 END) as condition_count2, " +
            "SUM(CASE WHEN ph < 9 AND ph > 8 THEN 1 ELSE 0 END) AS condition_count3, " +
            "SUM(CASE WHEN dissolved_oxygen < 6 THEN 1 ELSE 0 END) as condition_count4 " +
            "FROM water_quality")
    Map<String,Object> getNormalBy();

    //从来源省市入手
    @Select("SELECT Province_City, COUNT(*) AS province_city_count " +
            "FROM water_quality " +
            "GROUP BY Province_City")
    List<Map<String, Object>> getProvinceCityCount();

    //从排污流域分析
    @Select("SELECT Watershed_River, COUNT(*) AS watershed_count " +
            "FROM water_quality " +
            "GROUP BY Watershed_River")
    List<Map<String,Object>> getWatershedCount();


    //获得监测点数据和水温等信息
    @Select("SELECT Site_Condition as siteCondition, Section_Point as sectionPoint,Mid, Water_Temperature as waterTemperature, Conductivity, Monitoring_Time as monitoringTime FROM water_quality ORDER BY Mid DESC")
    List<Map<String,Object>> getWaterQualityData();

    //获得获取总磷数据
    @Select("SELECT Mid, Total_Phosphorus as totalPhosphorus FROM water_quality ")
    List<Map<String,Object>> getPhosphorusData();


/*数据管理*/
    //页面数据查询，暂无
    // @Select("select mid ,water_temperature, turbidity, dissolved_oxygen, conductivity, ph from water_quality where mid=#{mid}")
    @Select("SELECT mid ,water_temperature, turbidity, dissolved_oxygen, conductivity, ph FROM water_quality LIMIT #{startPage}, 10")
    Collection<WaterQuality> getAllInfo(int startPage);

    //获得页面信息(地表水水质)
    @Select("SELECT * FROM water_quality")
    List<Map<String,Object>> getAllWaterInfo();

    // 进行删除操作:deleteWaterInfo
    @Delete("DELETE FROM water_quality WHERE Mid = #{id}")
    void deleteWaterInfo(Long id);
}
